﻿Imports System.IO
Imports System.Text
Imports Microsoft
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports DataObject
Imports Microsoft.ApplicationBlocks.Data
Imports Microsoft.SqlServer.Management.Smo
Imports BusinessObject
Imports Microsoft.SqlServer.Management.Common

Public Class frmCreateNewDatabase

    Dim tabSelected As String = ""
    Dim sNameServer As String = ""
    Dim sUserName As String = ""
    Dim sPassword As String = ""
    Dim oTable As New Data.DataTable
    Dim oConnect As New SqlConnection
    Dim oSqlCmd As New SqlCommand
    Dim oSqlDa As New SqlDataAdapter
    Dim conn As SqlConnection

    Private Sub LoadControl()
        If (tabSelected = "tabChonMayChu") Then
            lblName.Text = "1. Chọn máy chủ"
            Me.AcceptButton = btnTiepTheo
            btnThucHien.Enabled = False
        ElseIf (tabSelected = "tabLoaiDLKT") Then
            lblName.Text = "2. Chọn loại dữ liệu"
            Me.AcceptButton = btnTiepTheo
            btnThucHien.Enabled = False
        ElseIf (tabSelected = "tabLuuDL") Then
            lblName.Text = "3. Chọn nơi lưu dữ liệu"
            Me.AcceptButton = btnTiepTheo
            btnTiepTheo.Enabled = True
            btnThucHien.Enabled = False
        ElseIf (tabSelected = "tabThucHien") Then
            lblName.Text = "4. Thực hiện tạo dữ liệu"
            lblServer.Text = "Tên máy chủ: " & cboServer.Text
            lblDatabase.Text = "Tên dữ liệu: " & txtTenDL.Text
            lblNoiLuuDuLieu.Text = "Nơi lưu dữ liệu: " & txtNoiLuuDL.Text
            btnThucHien.Enabled = True
            btnTiepTheo.Enabled = False
            Me.AcceptButton = btnThucHien
        End If
    End Sub

    Private Sub GetServerList()
        'Load dữ liệu cho combobox cboServer : Tìm các server tồn tại trong máy
        With cboServer
            .Items.Clear()
        End With
        oTable = SmoApplication.EnumAvailableSqlServers(False)
        For Each oRow As Data.DataRow In oTable.Rows
            sNameServer = oRow("Server").ToString
            If (Not Convert.IsDBNull(oRow("Instance")) And oRow("Instance").ToString().Length > 0) Then
                sNameServer += "\" & oRow("Instance").ToString()
            ElseIf cboServer.Items.IndexOf(sNameServer) < 0 Then
                cboServer.Items.Add(sNameServer)
            ElseIf cboServer.Items.Count > 0 Then
                cboServer.SelectedIndex = 0
            End If
        Next
    End Sub

    Private Function ValidData() As Boolean
        Try
            If (tabSelected = "tabChonMayChu") Then
                If (cboServer.Text.Equals("") = True) Then
                    MessageBox.Show("Tên máy chủ lưu dữ liệu không được để trống!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    cboServer.Focus()
                    Return False
                End If
                If (cboServer.Text.Equals("") = False) Then
                    conn = New SqlConnection("Data Source=" & cboServer.Text & _
                            ";Initial Catalog=master;Persist Security Info=True;User ID=sa;Password=thangphuong")
                    conn.Open()
                End If
            End If
            If (tabSelected = "tabLuuDL") Then
                If (txtTenDL.Text.Equals("") = True) Then
                    MessageBox.Show("Tên cơ sở dữ liệu không được để trống!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    txtTenDL.Focus()
                    Return False
                End If
                If (txtNoiLuuDL.Text.Equals("") = True) Then
                    MessageBox.Show("Đường dẫn lưu dữ liệu không được để trống!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    txtNoiLuuDL.Focus()
                    Return False
                End If
                If (txtTenDL.Text.Equals("") = False) Then
                    If (CheckDatabaseName() = False) Then
                        MessageBox.Show("Tên cơ sở dữ liệu đã tồn tại! Vui lòng nhập tên cơ sở dữ liệu khác", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                        txtTenDL.Focus()
                        Return False
                    End If
                End If
                If (txtNoiLuuDL.Text.Equals("") = False) Then
                    If (Directory.Exists(txtNoiLuuDL.Text) = False) Then
                        MessageBox.Show("Đường dẫn lưu dữ liệu không tồn tại! Vui lòng chọn lại đường dẫn khác", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                        btnChonTepTin.Focus()
                        Return False
                    End If
                End If
            End If
        Catch ex As SqlException
            Select Case ex.ErrorCode
                Case -2146232060
                    MessageBox.Show("Máy chủ không hợp lệ! Vui lòng chọn máy chủ khác", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    cboServer.Focus()
                    Return False
                Case Else
                    MessageBox.Show(ex.ToString)
            End Select
        End Try
        Return True
    End Function

    Private Function CheckDatabaseName() As Boolean
        Dim ds As New DataSet
        Dim sql As String = "SELECT name  FROM sys.databases WHERE Name Like N'" & txtTenDL.Text & "'"
        SqlHelper.FillDataset(conn.ConnectionString, CommandType.Text, sql, ds, New String() {"DBName"})
        If (ds.Tables("DBName").Rows.Count = 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Function AddNewDatabase() As Boolean
        Try
            Dim ds As New DataSet
            Dim SqlParam As SqlParameter() = {New SqlParameter("@pDBName", SqlDbType.NVarChar, 50), _
                                              New SqlParameter("@pDBNameBackUp", SqlDbType.NVarChar, 50), _
                                              New SqlParameter("@pFolderPath", SqlDbType.VarChar, 2000), _
                                              New SqlParameter("@pFolderPathBackUp", SqlDbType.VarChar, 2000)}
            SqlParam(0).Value = txtTenDL.Text
            SqlParam(1).Value = DBBackUpName
            SqlParam(2).Value = txtNoiLuuDL.Text
            SqlParam(3).Value = PathApplication + "\Data\QLHD_NEW_06052012.bak"
            SqlHelper.ExecuteScalar(conn.ConnectionString, CommandType.StoredProcedure, "Proc_Create_Database", SqlParam)
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return False
        End Try
        Return True
    End Function

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub frmCreateNewDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        tabSelected = "tabChonMayChu"
        tabMain.Tabs.Item("tabChonMayChu").Selected = True
        cboServer.Text = "ADMIN-PC\MSSQLSERVER2005"
        Me.AcceptButton = btnTiepTheo
        txtTenDL.Text = "QuanLyHopDong_2012"
        txtNoiLuuDL.Text = "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data"
        With dglFolder
            .RootFolder = Environment.SpecialFolder.MyComputer
            .Description = "Chọn thư mục"
        End With
    End Sub

    Private Sub btnTiepTheo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTiepTheo.Click
        If (tabSelected.Equals("tabChonMayChu") = True) Then
            ''Kiểm tra dữ liệu đã nhập chưa
            If (ValidData() = True) Then
                tabMain.Tabs.Item("tabLoaiDLKT").Selected = True
                tabSelected = "tabLoaiDLKT"
                LoadControl()
            End If
        ElseIf (tabSelected.Equals("tabLoaiDLKT") = True) Then
            tabMain.Tabs.Item("tabLuuDL").Selected = True
            tabSelected = "tabLuuDL"
            LoadControl()
        ElseIf (tabSelected.Equals("tabLuuDL") = True) Then
            If ValidData() = True Then
                tabMain.Tabs.Item("tabThucHien").Selected = True
                tabSelected = "tabThucHien"
                LoadControl()
                btnThucHien.Focus()
            End If
        End If
    End Sub

    Private Sub btnQuayLai_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuayLai.Click
        If (tabSelected.Equals("tabLoaiDLKT") = True) Then
            tabMain.Tabs.Item("tabChonMayChu").Selected = True
            tabSelected = "tabChonMayChu"
            LoadControl()
        ElseIf (tabSelected.Equals("tabLuuDL") = True) Then
            tabMain.Tabs.Item("tabLoaiDLKT").Selected = True
            tabSelected = "tabLoaiDLKT"
            LoadControl()
        ElseIf (tabSelected.Equals("tabThucHien") = True) Then
            tabMain.Tabs.Item("tabLuuDL").Selected = True
            tabSelected = "tabLuuDL"
            LoadControl()
        End If
    End Sub

    Private Sub cboServer_BeforeDropDown(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles cboServer.BeforeDropDown
        Try
            If cboServer.Items.Count = 0 Then
                Me.Cursor = Cursors.WaitCursor
                GetServerList()
                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        Finally
            Me.Cursor = Cursors.Default
        End Try

    End Sub

    Private Sub btnChonTepTin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChonTepTin.Click
        If dglFolder.ShowDialog() = Windows.Forms.DialogResult.OK Then
            txtNoiLuuDL.Text = dglFolder.SelectedPath.ToString()
        End If
    End Sub

    Private Sub btnThucHien_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThucHien.Click
        If (AddNewDatabase() = True) Then
            If MessageBox.Show("Tạo dữ liệu thành công! Ấn Ok để đăng nhập vào chương trình", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information) = Windows.Forms.DialogResult.OK Then
                Dim f As frmDangNhap = New frmDangNhap()
                With f
                    .cboServer.Text = cboServer.Text
                    .cboDatabase.Text = txtTenDL.Text
                    .txtUsername.Text = ""
                    .Show()
                End With
                Me.Close()
            End If
        End If
    End Sub

End Class